﻿using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System;
using LumenWorks.Framework.IO.Csv;



namespace LP_C_Sharp_2011_2012
{
    class CreateDatabase
    {
        public void createDB()
        {
            Console.WriteLine("começou");
            SQLiteConnection connection = new SQLiteConnection("Data Source=Rebides.db");
            connection.Open();
            SQLiteTransaction transaction = connection.BeginTransaction();
            SQLiteCommand command = connection.CreateCommand();

            command.CommandText = "DROP TABLE IF EXISTS teachers_information";
            command.ExecuteNonQuery();

            command.CommandText = "CREATE TABLE IF NOT EXISTS teachers_information (teacher_code integer, teacher_name text," +
                                                        "last_degree text,course text,category text," +
                                                        "regime text,year integer," +
                                                        "establishment_type text," +
                                                        "establishment_name text)";
            command.ExecuteNonQuery();




            fillDB(connection, command, transaction);

        }

        public void fillDB(SQLiteConnection connection, SQLiteCommand command, SQLiteTransaction transaction)
        {


            for (int t = 0; t <= 10; t++)
            {

                CsvReader fileReader = new CsvReader(
                                     new StreamReader(".\\rebcsv\\reb" + t + ".csv"), true);


                command.CommandText = "INSERT INTO teachers_information (teacher_code, teacher_name,last_degree,course,category,regime,year,establishment_type,establishment_name) VALUES(?,?,?,?,?,?,?,?,?)";

                int numFields = 9;
                for (int i = 0; i < numFields; i++)
                {
                    command.Parameters.Add(command.CreateParameter());
                }


                while (fileReader.ReadNextRecord())
                {

                    for (int j = 0; j < numFields; j++)
                    {
                        command.Parameters[j].Value = fileReader[j];
                    }

                    command.ExecuteNonQuery();
                }

            }

            transaction.Commit();

            connection.Close();
            Console.WriteLine("ACABOU");

        }
    }
}
